package com.intretech.umsin.code.generator.provider;


import com.intretech.umsin.code.generator.component.CamelCaseUtils;
import com.intretech.umsin.code.generator.component.DataBaseType;
import com.intretech.umsin.code.generator.entity.ColumnSchema;
import com.intretech.umsin.code.generator.entity.PrimaryKeySchema;
import com.intretech.umsin.code.generator.entity.TableSchema;
import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;

/**
 * @version V1.0
 * @ClassName: SqlSchemaProvider
 * @Description:( MS SqlServer 数据库实现)
 * @author: 李启联
 * @date: 2020-01-22 10:54
 * @Copyright: Copyright (c), 2020
 */
@Service
public class SqlSchemaProvider implements IDbSchemaProvider {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private TemplateMethodForGetDataType getDataTypeTemplateMethod;

    @Autowired
    private TemplateMethodForUnderlineToCamel underlineToCamelTemplateMethod;

    private String databaseName = "";

    public DataBaseType dataBaseType(){
        return DataBaseType.SQLSERVER;
    }

    private static Logger logger = org.slf4j.LoggerFactory.getLogger(SqlSchemaProvider.class);
    @Override
    public String getDatabaseName() {
        if (databaseName.equals("")) {
            Map<String, Object> result =  jdbcTemplate.queryForMap(" SELECT DB_NAME() AS DATABASE_NAME ");
            if(null != result && result.containsKey("DATABASE_NAME"))
                databaseName = result.get("DATABASE_NAME").toString();
        }
        return databaseName;
    }

    @Override
    public List<TableSchema> getTables(String... tables) {
        String sql ="";
        List<TableSchema> tableList = new ArrayList<>();
        BeanPropertyRowMapper<TableSchema> tableSchemaBeanPropertyRowMapper = BeanPropertyRowMapper.newInstance(TableSchema.class);
        if(tables.length <1) {
            sql = "SELECT OBJECT_NAME(O.ID) AS TABLE_NAME,USER_NAME(O.UID) AS OWNER,O.CRDATE AS CREATE_TIME,ISNULL(P.VALUE,'') AS TABLE_COMMENT \n" +
                    "FROM SYSOBJECTS O \n" +
                    "LEFT JOIN DBO.SYSPROPERTIES P ON O.ID=P.ID\n" +
                    "WHERE O.TYPE = N'U' OR O.TYPE = N'V' \n" +
                    "ORDER BY USER_NAME(O.UID), OBJECT_NAME(O.ID),O.TYPE";
            tableList = jdbcTemplate.query(sql,tableSchemaBeanPropertyRowMapper);
        }else{
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
            sql = "SELECT OBJECT_NAME(O.ID) AS TABLE_NAME,USER_NAME(O.UID) AS OWNER,O.CRDATE AS CREATE_TIME,ISNULL(P.VALUE,'') AS TABLE_COMMENT \n" +
                    "FROM SYSOBJECTS O \n" +
                    "LEFT JOIN DBO.SYSPROPERTIES P ON O.ID=P.ID\n" +
                    "WHERE (O.TYPE = N'U' OR O.TYPE = N'V')  AND OBJECT_NAME(O.ID) IN (:tables) \n" +
                    "ORDER BY USER_NAME(O.UID), OBJECT_NAME(O.ID),O.TYPE";
            Map<String,Object> param = new HashMap<>();
            param.put("tables",Arrays.asList(tables));
            tableList = namedParameterJdbcTemplate.query(sql,param,tableSchemaBeanPropertyRowMapper);
        }

        return tableList;
    }

    @Override
    public TableSchema getTableSchema(String tableName) {
        String sql = String.format("SELECT OBJECT_NAME(O.ID) AS TABLE_NAME,USER_NAME(O.UID) AS OWNER,O.CRDATE AS CREATE_TIME,ISNULL(P.VALUE,'') AS TABLE_COMMENT \n" +
                "FROM SYSOBJECTS O \n" +
                "LEFT JOIN DBO.SYSPROPERTIES P ON O.ID=P.ID\n" +
                "WHERE (O.TYPE = N'U' OR O.TYPE = N'V') AND OBJECT_NAME(O.ID) = '%s' \n" +
                "ORDER BY USER_NAME(O.UID), OBJECT_NAME(O.ID),O.TYPE", tableName);
        BeanPropertyRowMapper<TableSchema> tableSchemaBeanPropertyRowMapper = BeanPropertyRowMapper.newInstance(TableSchema.class);
        TableSchema tableSchema = jdbcTemplate.queryForObject(sql,tableSchemaBeanPropertyRowMapper);
        return tableSchema;
    }

    @Override
    public List<ColumnSchema> getTableColumns(String tableName) {
        String sql = String.format(
                "SELECT \r\n" +
                        "	CLMNS.[NAME] AS COLUMN_NAME,\r\n" +
                        "	CAST(CLMNS.ISNULLABLE AS BIT) AS IS_NULLABLE,\r\n" +
                        "	USRT.[NAME] AS DATA_TYPE,\r\n" +
                        "	CAST(CASE WHEN BASET.[NAME] IN (N'CHAR', N'VARCHAR', N'BINARY', N'VARBINARY', N'NCHAR', N'NVARCHAR') THEN CLMNS.PREC ELSE CLMNS.LENGTH END AS INT) AS [LENGTH],\r\n" +
                        "	EXT.[VALUE] AS COLUMM_COMMENT,	\r\n" +
                        "	TBL.[NAME] AS TABLE_NAME\r\n" +
                        "FROM DBO.SYSOBJECTS AS TBL WITH (NOLOCK)\r\n" +
                        "	INNER JOIN DBO.SYSCOLUMNS AS CLMNS WITH (NOLOCK) ON CLMNS.ID=TBL.ID\r\n" +
                        "	LEFT JOIN DBO.SYSTYPES AS USRT WITH (NOLOCK) ON USRT.XUSERTYPE = CLMNS.XUSERTYPE\r\n" +
                        "	LEFT JOIN DBO.SYSTYPES AS BASET WITH (NOLOCK) ON BASET.XUSERTYPE = CLMNS.XTYPE AND BASET.XUSERTYPE = BASET.XTYPE\r\n" +
                        "	LEFT JOIN SYS.EXTENDED_PROPERTIES AS EXT ON (CLMNS.ID=EXT.MAJOR_ID AND CLMNS.COLID =EXT.MINOR_ID AND EXT.NAME= 'MS_DESCRIPTION' ) \r\n" +
                        "WHERE (TBL.[TYPE] = 'U' OR TBL.[TYPE] = 'V') AND TBL.[NAME]='%s' \r\n" +
                        "ORDER BY TBL.[NAME], CLMNS.COLORDER",
                tableName);
        List<ColumnSchema> colList = new ArrayList<>();
        try {
            BeanPropertyRowMapper<ColumnSchema> rm = BeanPropertyRowMapper.newInstance(ColumnSchema.class);
            colList = jdbcTemplate.query(sql,rm);
        } catch (Exception e) {
            logger.error("getTableColumns",e);
        }
        return colList;
    }

    @Override
    public List<PrimaryKeySchema> getTablePrimaryKey(String tableName) {
        //String execSql = String.format("exec %s %s", "SP_PKEYS", tableName);
        return jdbcTemplate.execute(new CallableStatementCreator() {
                    @Override
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        String storedProc = "{call sp_pkeys (?)}";
                        CallableStatement cs = con.prepareCall(storedProc);
                        cs.setString(1, tableName);
                        return cs;
                    }
                },
                new CallableStatementCallback<List<PrimaryKeySchema>>() {
                    @Override
                    public List<PrimaryKeySchema> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                        List<PrimaryKeySchema> pkList = new ArrayList<>();
                        ResultSet rs = cs.executeQuery();
                        ResultSetMetaData rmd = rs.getMetaData();
                        int columnCount = rmd.getColumnCount();
                        String colName="";
                        Object colVal = null;
                        while (rs.next()) {
                            PrimaryKeySchema pkSchema = new PrimaryKeySchema();
                            for (int i = 1; i <= columnCount; i++) {
                                colName = rmd.getColumnName(i);
                                if(colName.equalsIgnoreCase("COLUMN_NAME"))
                                    pkSchema.setColumnName(rs.getObject(i).toString());
                                if(colName.equalsIgnoreCase("TABLE_NAME"))
                                    pkSchema.setTableName(rs.getObject(i).toString());
                                pkList.add(pkSchema);
                            }
                        }
                        rs.close();
                        return pkList;
                    }
                }
        );
    }

    @Override
    public String getEntityPrimaryKeyProperties(String tableName) {
        return CamelCaseUtils.underlineToCamel(getTablePrimaryKey(tableName).get(0).getColumnName().toLowerCase());
    }

    /**
     * 准备好渲染到模板上的数据
     * @param tableName 数据库表名称
     * @return
     */
    @Override
    public Map<String,Object> prepareDataModel(String tableName){
        Map<String, Object> dataModel = new LinkedHashMap<String, Object>();
        dataModel.put("sourceTable", tableName);
        dataModel.put("sourceTableDesc", this.getTableSchema(tableName).getTableComment());
        dataModel.put("columns", getTableColumns(tableName));
        dataModel.put("primaryKey",getTablePrimaryKey(tableName));
        dataModel.put("primaryKeyProperties", getEntityPrimaryKeyProperties(tableName));

        dataModel.put("underlineToCamel", underlineToCamelTemplateMethod);
        dataModel.put("getDataType", getDataTypeTemplateMethod);
        dataModel.put("dataBaseType",DataBaseType.SQLSERVER);

        return dataModel;
    }

    @Override
    public String getDataType(String type) {
        String t = type.toLowerCase();
        switch (t) {
            case "nchar":
            case "varchar":
            case "varchar(max)":
            case "text":
            case "nvarchar":
            case "nvarchar(max)":
            case "ntext":
            case "uniqueidentifier":
            case "xml":
                return "String";

            case "bigint":
                return "Long";

            case "timestamp":
            case "binary":
            case "image":
            case "varbinary(max)":
                return "byte[]";

            case "float":
                return "Double";
            case "int":
                return "Integer";
            case "bit":
                return "Boolean";
            case "numeric":
            case "decimal":
                return "java.math.BigDecimal";
            case "real":
                return "Float";

            case "smallint":
            case "tinyint":
                return "short";

            case "date":
            case "datetime":
            case "datetime2":
            case "smalldatetime":
                return "Date";

            default:
                return "Object";
        }
    }
}